﻿using System;
using System.Data;
using MySql.Data.MySqlClient;

namespace FiddlerEx_Inspector
{
    public class MysqlSqlDBHelper
    {
        //打开数据库
        public MySqlConnection MysqlOpen(string connStr)
        {
            MySqlConnection Conn = new MySqlConnection(connStr);
            try
            {
                Conn.Open();
                return Conn;
            }
            catch (Exception e)
            {
                Fiddler.FiddlerApplication.Log.LogString("连接数据库失败：" + e);
                return null;
            }
        }

        //关闭数据库
        public void MysqlClose(MySqlConnection Conn)
        {
            if (Conn != null)
            {
                Conn.Close();
                Conn.Dispose();
            }
            GC.Collect();
        }

        //执行sql语句
        public int RunSql(string connStr, string sql, Array paras)
        {
            MySqlConnection Conn = this.MysqlOpen(connStr);
            MySqlCommand cmd = new MySqlCommand(sql,Conn);
            cmd.Parameters.AddRange(paras);
            try
            {
                int res = cmd.ExecuteNonQuery();
                this.MysqlClose(Conn);
                return res;
            }
            catch (Exception e)
            {
                Fiddler.FiddlerApplication.Log.LogString("执行sql失败：" + sql + "\n" + e);
                this.MysqlClose(Conn);
                return 0;
            }
        }

        // 返回MySql语句执行结果的第一行第一列
        public string Get_Row1_Col1_Value(string sql, string ConnStr)
        {
            MySqlConnection Conn = MysqlOpen(ConnStr);
            string result = "";
            MySqlDataReader Dr;
            try
            {
                Dr = Create_Cmd(sql, Conn).ExecuteReader();
                if (Dr.Read())
                {
                    result = Dr.GetString(0);
                    Dr.Close();
                }
                else
                {
                    result = "";
                    //Fiddler.FiddlerApplication.Log.LogString("执行SQL返回结果为空：" + SQL);
                    Dr.Close();
                }
            }
            catch (Exception e)
            {
                Fiddler.FiddlerApplication.Log.LogString("执行sql失败：" + sql + "\n" + e);
            }

            MysqlClose(Conn);
            return result;
        }

        // 运行MySql语句返回 MySqlDataAdapter对象 
        internal MySqlDataAdapter Get_Adapter(string SQL, string ConnStr)
        {
            MySqlConnection Conn = this.MysqlOpen(ConnStr);
            MySqlDataAdapter Da = new MySqlDataAdapter(SQL, Conn);
            return Da;
        }

        // 运行MySql语句,返回DataSet对象
        public DataSet Get_DataSet(string sql, string ConnStr, DataSet Ds)
        {
            MySqlDataAdapter Da = Get_Adapter(sql, ConnStr);
            try
            {
                Da.Fill(Ds);
            }
            catch (Exception e)
            {
                Fiddler.FiddlerApplication.Log.LogString("执行sql失败：" + sql + "\n" + e);
            }
            return Ds;
        }

        // 运行MySql语句,返回DataSet对象
        public DataSet Get_DataSet(string sql, string ConnStr, DataSet Ds, string tablename)
        {
            MySqlDataAdapter Da = Get_Adapter(sql, ConnStr);
            try
            {
                Da.Fill(Ds, tablename);
            }
            catch (Exception e)
            {
                Fiddler.FiddlerApplication.Log.LogString("执行sql失败：" + sql + "\n" + e);
            }
            return Ds;
        }

        // 生成Command对象 
        internal MySqlCommand Create_Cmd(string SQL, MySqlConnection Conn)
        {
            MySqlCommand Cmd = new MySqlCommand(SQL, Conn);
            return Cmd;
        }
    }
}
